﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Data.SqlTypes;
using DTO = Microsoft.MSE.Catalog.Contracts;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class RuntimeServer 
    {

        public static Guid CreateRuntimeServer(DTO.RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogURIParam = new SqlParameter("@CatalogURI", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogBindingParam = new SqlParameter("@CatalogBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter NotificationURIParam = new SqlParameter("@NotificationURI", SqlDbType.NVarChar, 400);
                    SqlParameter NotificationBindingParam = new SqlParameter("@NotificationBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter HostNameParam = new SqlParameter("@HostName", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerURIParam = new SqlParameter("@BrokerURI", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerBindingParam = new SqlParameter("@BrokerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter ListenerParam = new SqlParameter("@ListenerURI", SqlDbType.NVarChar, 400);
                    SqlParameter ListenerBindingParam = new SqlParameter("@ListenerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = runtimeServer.Name;

                    if (string.IsNullOrEmpty(runtimeServer.CatalogURI) == false)
                    {
                        CatalogURIParam.Value = runtimeServer.CatalogURI;
                        CatalogBindingParam.Value = runtimeServer.CatalogBinding.ID;
                    }
                    else
                    {
                        CatalogURIParam.Value = null;
                        CatalogBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.NotificationURI) == false)
                    {
                        NotificationURIParam.Value = runtimeServer.NotificationURI;
                        NotificationBindingParam.Value = runtimeServer.NotificationBinding.ID;
                    }
                    else
                    {
                        NotificationURIParam.Value = null;
                        NotificationBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.BrokerURI) == false)
                    {
                        BrokerURIParam.Value = runtimeServer.BrokerURI;
                        BrokerBindingParam.Value = runtimeServer.BrokerBinding.ID;
                    }
                    else
                    {
                        BrokerURIParam.Value = null;
                        BrokerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.ListenerURI) == false)
                    {
                        ListenerParam.Value = runtimeServer.ListenerURI;
                        ListenerBindingParam.Value = runtimeServer.ListenerBinding.ID;
                    }
                    else
                    {
                        ListenerParam.Value = null;
                        ListenerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.HostName) == false)
                    {
                        HostNameParam.Value = runtimeServer.HostName;
                    }
                    else
                    {
                        HostNameParam.Value = null;
                    }

                    if (runtimeServer.Policy != null)
                    {
                        PolicyIDParam.Value = runtimeServer.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    DescriptionParam.Value = runtimeServer.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(CatalogURIParam);
                    cmd.Parameters.Add(NotificationURIParam);
                    cmd.Parameters.Add(HostNameParam);
                    cmd.Parameters.Add(BrokerURIParam);
                    cmd.Parameters.Add(ListenerParam);
                    cmd.Parameters.Add(CatalogBindingParam);
                    cmd.Parameters.Add(NotificationBindingParam);
                    cmd.Parameters.Add(BrokerBindingParam);
                    cmd.Parameters.Add(ListenerBindingParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.RuntimeServer UpdateRuntimeServer(DTO.RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogURIParam = new SqlParameter("@CatalogURI", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogBindingParam = new SqlParameter("@CatalogBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter NotificationURIParam = new SqlParameter("@NotificationURI", SqlDbType.NVarChar, 400);
                    SqlParameter NotificationBindingParam = new SqlParameter("@NotificationBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter HostNameParam = new SqlParameter("@HostName", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerURIParam = new SqlParameter("@BrokerURI", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerBindingParam = new SqlParameter("@BrokerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter ListenerParam = new SqlParameter("@ListenerURI", SqlDbType.NVarChar, 400);
                    SqlParameter ListenerBindingParam = new SqlParameter("@ListenerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter ReceiveNotificationsParam = new SqlParameter("@ReceiveNotifications", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = runtimeServer.ID;
                    NameParam.Value = runtimeServer.Name;

                    if (string.IsNullOrEmpty(runtimeServer.CatalogURI) == false)
                    {
                        CatalogURIParam.Value = runtimeServer.CatalogURI;
                        CatalogBindingParam.Value = runtimeServer.CatalogBinding.ID;
                    }
                    else
                    {
                        CatalogURIParam.Value = null;
                        CatalogBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.NotificationURI) == false)
                    {
                        NotificationURIParam.Value = runtimeServer.NotificationURI;
                        NotificationBindingParam.Value = runtimeServer.NotificationBinding.ID;
                    }
                    else
                    {
                        NotificationURIParam.Value = null;
                        NotificationBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.BrokerURI) == false)
                    {
                        BrokerURIParam.Value = runtimeServer.BrokerURI;
                        BrokerBindingParam.Value = runtimeServer.BrokerBinding.ID;
                    }
                    else
                    {
                        BrokerURIParam.Value = null;
                        BrokerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.ListenerURI) == false)
                    {
                        ListenerParam.Value = runtimeServer.ListenerURI;
                        ListenerBindingParam.Value = runtimeServer.ListenerBinding.ID;
                    }
                    else
                    {
                        ListenerParam.Value = null;
                        ListenerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.HostName) == false)
                    {
                        HostNameParam.Value = runtimeServer.HostName;
                    }
                    else
                    {
                        HostNameParam.Value = null;
                    }

                    if (runtimeServer.Policy != null)
                    {
                        PolicyIDParam.Value = runtimeServer.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    DescriptionParam.Value = runtimeServer.Description;
                    ReceiveNotificationsParam.Value = runtimeServer.ReceiveNotifications;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(HostNameParam);
                    cmd.Parameters.Add(CatalogURIParam);
                    cmd.Parameters.Add(NotificationURIParam);
                    cmd.Parameters.Add(BrokerURIParam);
                    cmd.Parameters.Add(ListenerParam);
                    cmd.Parameters.Add(CatalogBindingParam);
                    cmd.Parameters.Add(NotificationBindingParam);
                    cmd.Parameters.Add(BrokerBindingParam);
                    cmd.Parameters.Add(ListenerBindingParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(ReceiveNotificationsParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return runtimeServer;
        }

        public static void DeleteRuntimeServer(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                //NOTE: No change tracking implemented here since db integrity requires removing endpoints from runtime first

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }


        public static DTO.RuntimeServer GetRuntimeServer(Guid ID, bool provideWSDL, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.RuntimeServer runtimeServer = new DTO.RuntimeServer();
            const string sprocname = "[ServiceEngine].[GetRuntimeServer]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            runtimeServer.ID = reader.GetGuid(0);
                            runtimeServer.Name = reader.GetString(1);

                            runtimeServer.Name = reader.IsDBNull(1) ? null : reader.GetString(1);

                            runtimeServer.CatalogURI = reader.IsDBNull(2) ? null : reader.GetString(2);
                            runtimeServer.CatalogBinding = reader.IsDBNull(3) ? null : CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(3), txn, conn));

                            runtimeServer.NotificationURI = reader.IsDBNull(4) ? null : reader.GetString(4);
                            runtimeServer.NotificationBinding = reader.IsDBNull(5) ? null : CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(5), txn, conn));

                            runtimeServer.BrokerURI = reader.IsDBNull(6) ? null : reader.GetString(6);
                            runtimeServer.BrokerBinding = reader.IsDBNull(7) ? null : CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(7), txn, conn));

                            runtimeServer.ListenerURI = reader.IsDBNull(8) ? null : reader.GetString(8);
                            runtimeServer.ListenerBinding = reader.IsDBNull(9) ? null : CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(9), txn, conn));

                            runtimeServer.HostName = reader.IsDBNull(10) ? null : reader.GetString(10);
                            runtimeServer.Description = reader.IsDBNull(11) ? null : reader.GetString(11);

                            if (reader.IsDBNull(12))
                            {
                                runtimeServer.Policy = null;
                            }
                            else
                            {
                                runtimeServer.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(12), txn, conn);                               
                            }
                            runtimeServer.ReceiveNotifications = reader.GetBoolean(13);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return runtimeServer;

        }

        public static DTO.RuntimeServerList ListRuntimeServers(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.RuntimeServerList list = new DTO.RuntimeServerList();
            const string sprocname = "[ServiceEngine].[ListRuntimeServers]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.RuntimeServers = new List<DTO.RuntimeServerListElement>();
                            while (reader.Read())
                            {
                                DTO.RuntimeServerListElement element = new DTO.RuntimeServerListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.RuntimeServers.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.RuntimeServerListElement> ListRuntimeServersByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.RuntimeServerListElement> list = new List<DTO.RuntimeServerListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.RuntimeServerListElement>();
                            while (reader.Read())
                            {
                                DTO.RuntimeServerListElement element = new DTO.RuntimeServerListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForEndpointID(Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForEndpointID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.GetGuid(1);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForResourceID(Guid resourceID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForResourceID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter ResourceIDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    ResourceIDParam.Value = resourceID;
                    cmd.Parameters.Add(ResourceIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.GetGuid(1);
                                element.OperationId = reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForOperationID(Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForOperationID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationIDParam.Value = operationID;
                    cmd.Parameters.Add(OperationIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.GetGuid(1);
                                element.OperationId = reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForOperationVersionID(Guid operationVersionID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForOperationVersionID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter opVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    opVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(opVersionIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.GetGuid(1);
                                element.OperationId = reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForBindingID(Guid bindingID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForBindingID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationIDParam.Value = bindingID;
                    cmd.Parameters.Add(OperationIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
                                element.OperationId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForPolicyID(Guid policyID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForPolicyID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    PolicyIDParam.Value = policyID;
                    cmd.Parameters.Add(PolicyIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
                                element.OperationId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForSystemInstanceID(Guid instanceID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForSystemInstanceID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter InstanceIDParam = new SqlParameter("@InstanceID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    InstanceIDParam.Value = instanceID;
                    cmd.Parameters.Add(InstanceIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
                                element.OperationId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DBEventDataItem> ListRuntimeEventDataForSystemID(Guid systemID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DBEventDataItem> list = new List<DTO.DBEventDataItem>();
            const string sprocname = "[ServiceEngine].[ListRuntimeEventDataForSystemID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SystemIDParam = new SqlParameter("@SystemID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    SystemIDParam.Value = systemID;
                    cmd.Parameters.Add(SystemIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.DBEventDataItem element = new DTO.DBEventDataItem();
                                element.RuntimeServerId = reader.GetGuid(0);
                                element.EndpointId = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
                                element.OperationId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.RuntimeServerNotificationList ListRuntimeServerNotificationAddresses(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.RuntimeServerNotificationList list = new DTO.RuntimeServerNotificationList();
            const string sprocname = "[ServiceEngine].[ListRuntimeServerNotificationAddresses]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.RuntimeServers = new List<DTO.RuntimeServerNotificationListElement>();
                            while (reader.Read())
                            {
                                DTO.RuntimeServerNotificationListElement element = new DTO.RuntimeServerNotificationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                element.NotificationUrl = reader.GetString(2);
                                element.BindingID = reader.GetGuid(3);
                                list.RuntimeServers.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }
    }
}
